﻿-- Function: public.timeserial_treedata(integer, integer, integer)

---DROP FUNCTION public.timeserial_treedata(integer, integer, integer);

CREATE OR REPLACE FUNCTION public.timeserial_treedata(
    v_type integer,
    v_timestruct_parent_id integer,
    v_parent_id integer,
    v_timeserialstatus_enum varchar,
    p_org_id integer)
  RETURNS json  AS
$BODY$
declare  
    v_datatree text;  
    v_cnt integer;
 v_timestructid integer;
 v_timeserialid integer;
 v_calendar_enum varchar(50);
 v_name varchar(50);
 v_min	integer;
 v_max	integer;
 v_timeserialmin	integer;
 v_timeserialmax	integer;
 --timestruct_refcursor CURSOR(p_timestruct_parent_id INTEGER) FOR select id,calendar_enum,name From timestruct where parent_id=p_timestruct_parent_id;
 --timeserial_refcursor CURSOR(p_parent_id INTEGER,p_timestructid integer) FOR 
 --  select id,cast(num as VARCHAR) || name  as title From timeserial where parent_id=p_parent_id and timestruct_id=p_timestructid;
begin   
   v_datatree ='[';
  if v_type=0 then
    select COALESCE(min(id),0),COALESCE(max(id),0),count(*)
    into v_min,v_max,v_cnt From timestruct where org_id =p_org_id and parent_id=v_timestruct_parent_id;
    if v_min>0 then
       while v_min <= v_max loop
         select id,calendar_enum,name 
         into v_timestructid,v_calendar_enum,v_name
         From timestruct where id=v_min;
	 if v_cnt=1 and v_timestruct_parent_id>0 then
-------------------------
            select COALESCE(min(id),0),COALESCE(max(id),0)
            into v_timeserialmin,v_timeserialmax
            From timeserial where parent_id=v_parent_id and timestruct_id=v_timestructid
            and (regexp_split_to_array (timeserialstatus_enum,','))::varchar[] <@ regexp_split_to_array (v_timeserialstatus_enum,',')::varchar[];
	    if v_timeserialmin>0 then
              while v_timeserialmin <= v_timeserialmax loop
                select id,cast(num as VARCHAR) || name  as title 
		into v_timeserialid,v_name		
                From timeserial where id=v_timeserialmin;
		if v_datatree='[' then
		      v_datatree = v_datatree || '{"title":"'|| v_name ||'","value":"'|| v_timeserialid ||'","children":' || timeserial_treedata(0,v_timestructid,v_timeserialid,v_timeserialstatus_enum) ||'}';
		else
		      v_datatree = v_datatree || ',{"title":"'|| v_name ||'","value":"'|| v_timeserialid ||'","children":' || timeserial_treedata(0,v_timestructid,v_timeserialid,v_timeserialstatus_enum) ||'}';
		end if;
		if v_timeserialmin < v_timeserialmax then
                  select COALESCE(min(id),0)
	          into v_timeserialmin
                  From timeserial where parent_id=v_parent_id and timestruct_id=v_timestructid and id>v_timeserialmin
                  and (regexp_split_to_array (timeserialstatus_enum,','))::varchar[] <@ regexp_split_to_array (v_timeserialstatus_enum,',')::varchar[];
                else
                  v_timeserialmin =v_timeserialmax+1;
                end if;
              end loop; 
	    end if; 
-------------------------
	  else
	    if v_datatree='[' then
	      v_datatree = v_datatree || '{"title":"'|| v_name ||'","value":"'|| v_calendar_enum ||'","disabled":true,"children":' || timeserial_treedata(1,v_timestructid,v_parent_id,v_timeserialstatus_enum) ||'}';
	    else
	      v_datatree = v_datatree || ',{"title":"'|| v_name ||'","value":"'|| v_calendar_enum ||'","disabled":true,"children":' || timeserial_treedata(1,v_timestructid,v_parent_id,v_timeserialstatus_enum) ||'}';
	    end if;
	  end if;
          if v_min < v_max then
            select COALESCE(min(id),0)
	    into v_min
            from timestruct  where org_id =p_org_id and parent_id=v_timestruct_parent_id and id>v_min;
          else
            v_min =v_max+1;
          end if;
        end loop; 
      end if;
   else
      select COALESCE(min(id),0),COALESCE(max(id),0)
      into v_timeserialmin,v_timeserialmax
      From timeserial where parent_id=v_parent_id and timestruct_id=v_timestruct_parent_id
      and (regexp_split_to_array (timeserialstatus_enum,','))::varchar[] <@ regexp_split_to_array (v_timeserialstatus_enum,',')::varchar[];
      if v_timeserialmin>0 then
        while v_timeserialmin <= v_timeserialmax loop
          select id,cast(num as VARCHAR) || name  as title 
	  into v_timeserialid,v_name		
          From timeserial where id=v_timeserialmin;
	  if v_datatree='[' then
	    v_datatree = v_datatree || '{"title":"'|| v_name ||'","value":"'|| v_timeserialid ||'","children":' || timeserial_treedata(0,v_timestruct_parent_id,v_timeserialid,v_timeserialstatus_enum) ||'}';
	  else
	    v_datatree = v_datatree || ',{"title":"'|| v_name ||'","value":"'|| v_timeserialid ||'","children":' || timeserial_treedata(0,v_timestruct_parent_id,v_timeserialid,v_timeserialstatus_enum) ||'}';
	  end if;
	  if v_timeserialmin < v_timeserialmax then
            select COALESCE(min(id),0)
	    into v_timeserialmin
            From timeserial where parent_id=v_parent_id and timestruct_id=v_timestruct_parent_id and id>v_timeserialmin
            and (regexp_split_to_array (timeserialstatus_enum,','))::varchar[] <@ regexp_split_to_array (v_timeserialstatus_enum,',')::varchar[];
          else
            v_timeserialmin =v_timeserialmax+1;
          end if;
        end loop; 
      end if; 
    end if;  
     v_datatree = v_datatree || ']';
   RETURN v_datatree::json; 
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
